package com.notebad.note;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class NotesDbManager {


	private static final String TAG = "NotesDbAdapter";

	/**
	 * NOTE TABLE COLUMNS
	 */
	public static final String KEY_ROWID = "_id";
	public static final String KEY_TITLE = "title";
	public static final String KEY_BODY = "body";
	public static final String KEY_DATE = "date";
	public static final String KEY_CATEGORY = "category";
	public static final String KEY_ADDRESS = "address";
	public static final String KEY_LATITUDE = "latitude";
	public static final String KEY_LONGITUDE = "longitude";
	public static final String KEY_PICURE_COUNTER = "picture_counter";
	public static final String KEY_SOUND_COUNTER = "sound_counter";

	/**
	 * CATEGORY TABLE COLUMNS
	 */
	public static final String KEY_CAT_ID = "_id";
	public static final String KEY_CAT_NAME = "name";

	private static final String DATABASE_NAME = "notebadDebug";
	private static final String NOTE_TABLE = "notes";
	private static final String CATEGORY_TABLE = "categories";
	private static final int DATABASE_VERSION = 13;

	/**
	 * Note table creation sql query
	 */
	private static final String CREATE_NOTE_TABLE_QUERY =
		"create table " + NOTE_TABLE + " (" + KEY_ROWID + " integer primary key autoincrement, "
		+ KEY_TITLE + " text not null, " + KEY_BODY + " text, " 
		+ KEY_DATE + " text not null, " + KEY_CATEGORY + " text, "+ KEY_ADDRESS 
		+ " text, " + KEY_LATITUDE + " double, " + KEY_LONGITUDE + " double, " 
		+ KEY_PICURE_COUNTER + " integer, " + KEY_SOUND_COUNTER + " integer);";

	/**
	 * Category table creation sql query
	 */
	private static final String CREATE_CATEGORY_TABLE_QUERY =
		"create table " + CATEGORY_TABLE + " (" + KEY_CAT_ID + " integer primary key autoincrement, "
		+ KEY_CAT_NAME + " text not null);";

	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;
	private final Context mCtx;

	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {

			db.execSQL(CREATE_NOTE_TABLE_QUERY);
			db.execSQL(CREATE_CATEGORY_TABLE_QUERY);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS "+ NOTE_TABLE);
			db.execSQL("DROP TABLE IF EXISTS "+ CATEGORY_TABLE);
			onCreate(db);
		}
	}

	/**
	 * Constructor
	 * 
	 * @param Context
	 */
	public NotesDbManager(Context ctx) {
		this.mCtx = ctx;
	}

	/**
	 * Open the notes database.
	 */
	public NotesDbManager open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}


	/**
	 * Create a new note 
	 */
	public long createNote(Note note) {

		// Date from now
		Date now = new Date ();
		SimpleDateFormat format = new SimpleDateFormat("dd MMM yyyy", Locale.FRENCH);

		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_TITLE, note.getTitle());
		initialValues.put(KEY_BODY, note.getBody());
		initialValues.put(KEY_DATE, format.format(now));
		initialValues.put(KEY_CATEGORY, note.getCategory());
		initialValues.put(KEY_ADDRESS, note.getAddress());
		initialValues.put(KEY_LATITUDE, note.getLatitude());
		initialValues.put(KEY_LONGITUDE, note.getLongitude());
		initialValues.put(KEY_PICURE_COUNTER, note.getLatitude());
		initialValues.put(KEY_SOUND_COUNTER, note.getLongitude());

		Log.i(TAG, "Creating a new note");
		return mDb.insert(NOTE_TABLE, null, initialValues);
	}

	/**
	 * Delete the note
	 */
	public boolean deleteNote(long rowId)
	{
		Log.i(TAG, "Deleting note which has " + rowId + "as id");
		return mDb.delete(NOTE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
	}

	/**
	 * Return a Cursor over the list of all notes in the database
	 */
	public Cursor fetchAllNotes(String[] filter) {
		
		StringBuffer request = new StringBuffer("(");
		
		for (int i = 0; i<filter.length; i++){
			request.append(KEY_TITLE);
			request.append(" like '%");
			request.append(filter[i]);
			request.append("%' or ");
			request.append(KEY_BODY);
			request.append(" like '%");
			request.append(filter[i]);
			request.append("%' or ");
			request.append(KEY_DATE);
			request.append(" like '%");
			request.append(filter[i]);
			request.append("%' or ");
			request.append(KEY_CATEGORY);
			request.append(" like '%");
			request.append(filter[i]);
			request.append("%' or ");
			request.append(KEY_ADDRESS);
			request.append(" like '%");
			request.append(filter[i]);
			if(i == filter.length - 1){
				request.append("%')");
			}
			else{
				request.append("%') and (");
			}
		}

		Cursor curs = mDb.query(NOTE_TABLE, new String[] {KEY_ROWID, KEY_TITLE, KEY_BODY, 
				KEY_DATE, KEY_CATEGORY, KEY_ADDRESS, KEY_LATITUDE, KEY_LONGITUDE, KEY_PICURE_COUNTER, KEY_SOUND_COUNTER}, 
				request.toString() , null, null, null, null);

		return curs;
	}

	/**
	 * Return a Cursor positioned at the note that matches the given rowId
	 */
	public Note fetchNote(long rowId) throws SQLException {

		Cursor mCursor = mDb.query(true, NOTE_TABLE, new String[] {KEY_ROWID, KEY_TITLE, KEY_BODY, 
				KEY_DATE, KEY_CATEGORY, KEY_ADDRESS, KEY_LATITUDE, KEY_LONGITUDE, KEY_PICURE_COUNTER, KEY_SOUND_COUNTER}, 
				KEY_ROWID + "=" + rowId, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}

		Note note = new Note(mCursor.getLong(mCursor.getColumnIndexOrThrow(KEY_ROWID)), mCursor.getString(mCursor.getColumnIndexOrThrow(KEY_DATE)), 
				mCursor.getString(mCursor.getColumnIndexOrThrow(KEY_CATEGORY)), mCursor.getString(mCursor.getColumnIndexOrThrow(KEY_TITLE)), 
				mCursor.getString(mCursor.getColumnIndexOrThrow(KEY_BODY)), mCursor.getDouble(mCursor.getColumnIndexOrThrow(KEY_LATITUDE)), 
				mCursor.getDouble(mCursor.getColumnIndexOrThrow(KEY_LONGITUDE)), mCursor.getString(mCursor.getColumnIndexOrThrow(KEY_ADDRESS)),
				mCursor.getInt(mCursor.getColumnIndexOrThrow(KEY_PICURE_COUNTER)), mCursor.getInt(mCursor.getColumnIndexOrThrow(KEY_SOUND_COUNTER)));
		mCursor.close();
		return note;
	}

	/**
	 * Update note
	 * 
	 */
	public boolean updateNote(Note note) {

		//now
		Date now = new Date ();
		SimpleDateFormat format = new SimpleDateFormat("dd MMM yyyy");

		ContentValues args = new ContentValues();
		args.put(KEY_TITLE, note.getTitle());
		args.put(KEY_BODY, note.getBody());
		args.put(KEY_DATE, format.format(now));
		args.put(KEY_CATEGORY, note.getCategory());
		args.put(KEY_ADDRESS, note.getAddress());
		args.put(KEY_LATITUDE, note.getLatitude());
		args.put(KEY_LONGITUDE, note.getLongitude());
		args.put(KEY_PICURE_COUNTER, note.getMaxIndexPicture());
		args.put(KEY_SOUND_COUNTER, note.getMaxIndexSound());

		Log.i(TAG, "Updating note which has " + note.getId() + "as id");
		return mDb.update(NOTE_TABLE, args, KEY_ROWID + "=" + note.getId(), null) > 0;
	}

	/**
	 * Returns Cursor with categories from database
	 */
	public Cursor getCategories()
	{
		Cursor curs = mDb.query(CATEGORY_TABLE, new String[] {KEY_CAT_ID, KEY_CAT_NAME}, null, null, null, null, null);
		return curs;
	}

	/**
	 * Add a category into database
	 */
	public long addCategory(String catName)
	{

		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_CAT_NAME, catName);

		Log.i(TAG, "Creating a new category");
		return mDb.insert(CATEGORY_TABLE, null, initialValues);
	}

	/**
	 * Delete a category with the given id
	 */
	public boolean deleteCategory(long rowId)
	{
		Log.i(TAG, "Deleting note which has " + rowId + "as id");
		return mDb.delete(CATEGORY_TABLE, KEY_CAT_ID + "=" + rowId, null) > 0;
	}
}
